package com.bringspring.visualdev.onlinedev.util.onlineDevUtil;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSONArray;
import com.bringspring.common.database.model.entity.DbLinkEntity;
import com.bringspring.common.database.model.DataSourceModel;
import com.bringspring.common.database.util.DbTypeUtil;
import com.bringspring.common.model.visiual.ComponentKeyConsts;
import com.bringspring.common.model.visiual.TableModel;
import com.bringspring.common.util.JsonUtil;
import com.bringspring.common.util.StringUtils;
import com.bringspring.common.util.context.SpringContext;
import com.bringspring.visualdev.base.util.VisualUtils;
import com.bringspring.visualdev.onlinedev.model.OnlineDevListModel.OnlineColumnChildFieldModel;
import com.bringspring.visualdev.onlinedev.model.OnlineDevListModel.OnlineColumnFieldModel;
import com.bringspring.visualdev.onlinedev.model.OnlineDevListModel.OnlineListSqlModel;
import com.bringspring.visualdev.onlinedev.model.OnlineDevListModel.VisualColumnSearchVO;
import lombok.Cleanup;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 *
 *  生成在线sql语句
 * @author 开发平台组
 * @version V3.2.8
 * @copyright 股份有限公司
 * @date  2021/11/8
 */
public class OnlineProductSqlUtils {
	private static DataSourceModel dataSourceModel = SpringContext.getBean(DataSourceModel.class);

	/**
	 * 生成列表查询sql
	 *
	 * @param sql
	 * @param tableModelList 表集合
	 * @param collect
	 * @param linkEntity
	 * @return
	 */
	public static StringBuilder getColumnListSql(StringBuilder sql, List<TableModel> tableModelList, List<String> collect, DbLinkEntity linkEntity) {
		try {
			collect=collect.stream().distinct().collect(Collectors.toList());

			@Cleanup Connection conn = VisualUtils.getDataConn(linkEntity);
			//主表
			TableModel mainTable = tableModelList.stream().filter(model -> model.getTypeId().equals("1" )).findFirst().orElse(null);
			//获取主键
			String pKeyName = VisualUtils.getpKey(conn, mainTable.getTable());
			//列表中区别子表正则
			String reg = "^[jsbos_]\\S*_jsbos\\S*";

			//列表主表字段
			List<String> mainTableFields = collect.stream().filter(s -> !s.matches(reg)).collect(Collectors.toList());
			mainTableFields.add(pKeyName);

			//列表子表字段
			List<OnlineColumnFieldModel> childFieldList = collect.stream().filter(s -> s.matches(reg)).map(child -> {
				OnlineColumnFieldModel fieldModel = new OnlineColumnFieldModel();
				String s1 = child.substring(child.lastIndexOf("jsbos_" )).replace("jsbos_", "" );
				String s2 = child.substring(child.indexOf("_" ) + 1, child.lastIndexOf("_jsbos" ));
				fieldModel.setTableName(s2);
				fieldModel.setField(s1);
				fieldModel.setOriginallyField(child);
				return fieldModel;
			}).collect(Collectors.toList());

			//取列表用到的表
			List<String> ColumnTableNameList = childFieldList.stream().map(t -> t.getTableName().toLowerCase()).collect(Collectors.toList());
			List<TableModel> tableModelList1 = tableModelList.stream().filter(t -> ColumnTableNameList.contains(t.getTable().toLowerCase())).collect(Collectors.toList());
			List<OnlineColumnChildFieldModel> classifyFieldList = new ArrayList<>(10);
			for (TableModel t : tableModelList1) {
				OnlineColumnChildFieldModel childFieldModel = new OnlineColumnChildFieldModel();
				childFieldModel.setTable(t.getTable());
				childFieldModel.setRelationField(t.getRelationField());
				childFieldModel.setTableField(t.getTableField());
				classifyFieldList.add(childFieldModel);
			}
			//取别名
			for (int i = 0; i < childFieldList.size(); i++) {
				OnlineColumnFieldModel model = childFieldList.get(i);
				model.setOtherName(model.getField() + "_" + i);
			}

			String mainSqlFields = mainTableFields.stream().collect(Collectors.joining("," ));
			String childFields = "";
			if (classifyFieldList.size() > 0) {
				mainSqlFields = mainTableFields.stream().map(m -> mainTable.getTable() + "." + m).collect(Collectors.joining("," ));
				childFields = "," + childFieldList.stream().map(c -> c.getTableName() + "." + c.getField() + "  " + c.getOtherName()).collect(Collectors.joining("," ));
			}
			OnlineListSqlModel listSqlModel = new OnlineListSqlModel();
			listSqlModel.setFields(mainSqlFields + childFields);
			listSqlModel.setMainTable(mainTable.getTable());
			listSqlModel.setPKeyName(pKeyName);

			//获取查询语句
			sql = getSqlByDatabase(classifyFieldList, listSqlModel, linkEntity);

		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
		}
		return sql;
	}

	public static StringBuilder getSqlByDatabase(List<OnlineColumnChildFieldModel> classifyFieldList, OnlineListSqlModel listSqlModel, DbLinkEntity linkEntity) {
		StringBuilder sql = new StringBuilder();
//		DataSourceUtil dataSourceUtil = SpringContext.getBean(DataSourceUtil.class);
		String mainTable = listSqlModel.getMainTable();

//		//金仓 oracle 需关闭大小写敏感
//		//数据源
//		if (linkEntity != null) {
//			if (DbTypeUtil.checkKingbase(linkEntity)) {
//				mainTable = linkEntity.getUserName() + "." + listSqlModel.getMainTable();
//			}
//		}
//		//默认数据源
//		else {
//			if (DbTypeUtil.checkKingbase(dataSourceUtil)) {
//				mainTable = linkEntity.getUserName() + "." + listSqlModel.getMainTable();
//			}
//		}
		{
			if (classifyFieldList.size() > 0) {
				sql.append("select " + listSqlModel.getFields() + " from" + " " + mainTable);
				for (OnlineColumnChildFieldModel model : classifyFieldList) {
					sql.append(" LEFT JOIN " + model.getTable() + " ON " + model.getTable() + "." + model.getTableField() + "=" + mainTable + "." + model.getRelationField() + " " );
				}
			} else {
				sql.append("select " + listSqlModel.getFields() + " from" + " " + mainTable);
			}
		}
		return sql;
	}

	/**
	 *
	 */
	public static StringBuilder getConditionSql(StringBuilder sql, DbLinkEntity linkEntity, List<VisualColumnSearchVO> searchVOList, String mainTable) {

		boolean isOracle = DbTypeUtil.checkOracle(dataSourceModel);
		if (linkEntity != null) {
			isOracle = linkEntity.getDbType().toLowerCase().contains("oracle" );
		}

		for (int k = 0; k < searchVOList.size(); k++) {
			VisualColumnSearchVO vo = searchVOList.get(k);
			String keyName = "keyName";
			String tableName = mainTable;
			if (Objects.nonNull(vo.getConfig())){
				keyName = vo.getConfig().getKeyName();
				tableName =  vo.getConfig().getTableName();
			}

			String searchType = vo.getSearchType();
			String vModel = vo.getVModel();
			String value = String.valueOf(vo.getValue());
			String format;

			Boolean isMultiple = vo.getMultiple() != null ? vo.getMultiple() : false;
			if (isMultiple || ComponentKeyConsts.CHECKBOX.equals(keyName) || ComponentKeyConsts.CASCADER.equals(keyName) || ComponentKeyConsts.CURRORGANIZE.equals(keyName)) {
				searchType = "2";
				if (value.contains("[")){
					List<String> mutipleList = JsonUtil.getJsonToList(value, String.class);
					value = mutipleList.get(mutipleList.size()-1);
				}
			}
			if ("1".equals(searchType)) {
				sql.append("AND " + tableName + "." + vModel + " = " + "'" + value + "'" );
			} else if ("2".equals(searchType)) {
				sql.append("AND " + tableName + "." + vModel + " like " + "'%" + value + "%'" );
			} else if ("3".equals(searchType)) {
				switch (keyName) {
					case ComponentKeyConsts.MODIFYTIME:
					case ComponentKeyConsts.CREATETIME:
					case ComponentKeyConsts.DATE:
						JSONArray timeStampArray = (JSONArray) vo.getValue();
						Long o1 = (Long) timeStampArray.get(0);
						Long o2 = (Long) timeStampArray.get(1);
						format = StringUtils.isEmpty(vo.getFormat()) ? "yyyy-MM-dd HH:mm:ss" : vo.getFormat();
						//时间戳转string格式
						String startTime = OnlinePublicUtils.getDateByFormat(o1, format);
						String endTime = OnlinePublicUtils.getDateByFormat(o2, format);
						//处理时间查询条件范围
						if (!"yyyy-MM-dd HH:mm:ss".equals(format) || vo.getFormat()==null){
							endTime = endTime.substring(0, 10);
						}
						String firstTimeDate = OnlineDatabaseUtils.getTimeFormat(startTime);
						String lastTimeDate = OnlineDatabaseUtils.getLastTimeFormat(endTime);

						if (isOracle) {
							sql.append("AND " + tableName + "." + vModel + " between to_date(" + "'" + firstTimeDate + "','yyyy-mm-dd hh24:mi:ss'" + ") AND to_date(" + "'" + lastTimeDate + "','yyyy-mm-dd hh24:mi:ss')" );
						} else {
							sql.append("AND " + tableName + "." + vModel + " between " + "'" + firstTimeDate + "'" + "AND " + "'" + lastTimeDate + "'" );
						}
						break;
					case ComponentKeyConsts.TIME:
						List<String> stringList = JsonUtil.getJsonToList(value, String.class);
						format = "HH:mm:ss";
						if (isOracle) {
							sql.append("AND " + tableName + "." + vModel + " between to_date(" + "'" + stringList.get(0) + "','" + format + "'" + ") AND to_date(" + "'" + stringList.get(1) + "','" + format + "')" );
						} else {
							sql.append("AND " + tableName + "." + vModel + " between " + "'" + stringList.get(0) + "'" + "AND " + "'" + stringList.get(1) + "'" );
						}
						break;
					case ComponentKeyConsts.NUM_INPUT:
					case ComponentKeyConsts.CALCULATE:
						Float firstValue = null;
						Float secondValue = null;
						JSONArray objects = (JSONArray) vo.getValue();
						for (int i = 0; i < objects.size(); i++) {
							Object n = objects.get(i);
							if (ObjectUtil.isNotEmpty(n)) {
								if (i == 0) {
									firstValue = Float.parseFloat(String.valueOf(n));
								} else {
									secondValue = Float.parseFloat(String.valueOf(n));
								}
							}
						}
						if (firstValue != null) {
							sql.append("AND " + tableName + "." + vModel + " >= " + firstValue);
						}
						if (secondValue != null) {
							sql.append("AND " + tableName + "." + vModel + " <= " + secondValue);
						}
						break;
					default:
						break;
				}
			}
		}
		return sql;
	}

	public static List<OnlineColumnFieldModel> getChildValue(List<String> collect) {
		List<OnlineColumnFieldModel> childFieldList =new ArrayList<>() ;
			//列表中区别子表正则
			String reg = "^[jsbos_]\\S*_jsbos\\S*";

			//列表主表字段
			List<String> mainTableFields = collect.stream().filter(s -> !s.matches(reg)).collect(Collectors.toList());
			List<OnlineColumnFieldModel> mainList = mainTableFields.stream().map(c -> {
				OnlineColumnFieldModel fieldModel = new OnlineColumnFieldModel();
				fieldModel.setOriginallyField(c);
				fieldModel.setOtherName(c);
				return fieldModel;
			}).collect(Collectors.toList());

			//列表子表字段
			childFieldList = collect.stream().filter(s -> s.matches(reg)).map(child -> {
				OnlineColumnFieldModel fieldModel = new OnlineColumnFieldModel();
				String s1 = child.substring(child.lastIndexOf("jsbos_" )).replace("jsbos_", "" );
				String s2 = child.substring(child.indexOf("_" ) + 1, child.lastIndexOf("_jsbos" ));
				fieldModel.setTableName(s2);
				fieldModel.setField(s1);
				fieldModel.setOriginallyField(child);
				return fieldModel;
			}).collect(Collectors.toList());

			//取别名
			for (int i = 0; i < childFieldList.size(); i++) {
				OnlineColumnFieldModel model = childFieldList.get(i);
				model.setOtherName(model.getField() + "_" + i);
			}
			childFieldList.addAll(mainList);
			return childFieldList;
		}
}